//package com.njcky.service;
//
//
//import com.fasterxml.jackson.core.type.TypeReference;
//import com.fasterxml.jackson.databind.ObjectMapper;
//import com.njcky.model.dto.QueryPipelineLengthDTO;
//import com.njcky.utils.db.DBHelper;
//import com.njcky.utils.page.HttpStatus;
//import com.njcky.utils.page.StringUtils;
//import com.njcky.utils.page.entity.TableDataInfo;
//import lombok.extern.slf4j.Slf4j;
//import org.apache.commons.lang3.ObjectUtils;
//import org.springframework.beans.factory.annotation.Autowired;
//import org.springframework.beans.factory.annotation.Value;
//import org.springframework.stereotype.Service;
//import org.springframework.util.CollectionUtils;
//
//import java.math.BigDecimal;
//import java.math.RoundingMode;
//import java.sql.*;
//import java.text.SimpleDateFormat;
//import java.util.*;
//import java.util.Date;
//import java.util.concurrent.CompletableFuture;
//import java.util.regex.Matcher;
//import java.util.regex.Pattern;
//import java.util.stream.Collectors;
//
//import static net.sf.jsqlparser.parser.feature.Feature.select;
//
//@Service
//@Slf4j
//public class SpatialQueryService {
//
//    private static final ObjectMapper objectMapper = new ObjectMapper();
//
//    @Value("${pgLink.url}")
//    String pgUrl;
//    @Value("${pgLink.username}")
//    String pgUserName;
//    @Value("${pgLink.password}")
//    String pgPassword;
//
//
//
//
//    @Autowired
//    JianJunRoadDemoService jianJunRoadDemoService;
//
//    /**
//     * 计算连通管线的距离
//     *
//     * @param dto
//     * @return
//     */
////
////    public Map<String, Object> queryMain(QueryPipelineLengthDTO dto) {
////        // 1. 参数校验与基础信息获取（提前失败，避免无效操作）
////        if (dto == null || dto.getTables() == null || dto.getTables().isEmpty()) {
////            log.warn("查询参数无效：{}", dto);
////            return Collections.emptyMap();
////        }
////        String tableName = dto.getTables().get(0);
////
////        try {
////            // 2. 优化起点/终点查询：合并调用逻辑，减少重复代码；只查询必要字段（x,y）
////            Map<String, Object> startMap = getNearestPoint(dto.getFirstPoslon(), dto.getFirstPoslat(), tableName);
////            Map<String, Object> endMap = getNearestPoint(dto.getEndPoslon(), dto.getEndPoslat(), tableName);
////            if (startMap == null || endMap == null) {
////                log.warn("未查询到起点或终点数据");
////                return Collections.emptyMap();
////            }
////            // 3. 提取坐标（避免频繁toString()，直接用原始类型）
////            String pointStartX = String.valueOf(startMap.get("x"));
////            String pointStartY = String.valueOf(startMap.get("y"));
////            String pointEndX = String.valueOf(endMap.get("x"));
////            String pointEndY = String.valueOf(endMap.get("y"));
////
////            // 4. 优化bsmList获取：直接查询bsm列表，避免先查全量Map再提取（核心优化）
////            List<String> bsmList = getBsmList(dto);
////            if (bsmList.isEmpty()) {
////                log.warn("未查询到有效bsm列表");
////                return Collections.emptyMap();
////            }
////            // 5. 并行处理计算与geoJson获取（无依赖操作，并行执行减少总耗时）
////            CompletableFuture<Map<String, Object>> lengthFuture = CompletableFuture.supplyAsync(() ->
////                    calculatePipeLength(pointStartX, pointStartY, pointEndX, pointEndY, bsmList, tableName)
////            );
////            CompletableFuture<Map<String, Object>> geoJsonFuture = CompletableFuture.supplyAsync(() ->
////                    getGeoJson(tableName, bsmList)
////            );
////
////            // 6. 合并结果
////            Map<String, Object> objectMap = lengthFuture.get();
////            objectMap.put("geoJsonData", geoJsonFuture.get());
////
////            return objectMap;
////        } catch (Exception e) {
////            log.error("管道查询主流程异常", e);
////            return Collections.emptyMap();
////        }
////    }
////
////    // 抽取公共方法：查询最近点（减少重复代码）
////    private Map<String, Object> getNearestPoint(String lon, String lat, String tableName) {
////        List<Map<String, Object>> mapList = queryNearestLinesOne(lon, lat, tableName);
////        return (mapList != null && !mapList.isEmpty()) ? mapList.get(0) : null;
////    }
////
////    // 优化：直接查询bsm列表（避免全量字段查询）
////    private List<String> getBsmList(QueryPipelineLengthDTO dto) {
////        // 若connectivityPublic可修改，建议直接返回List<String>；否则此处转换
////        List<Map<String, Object>> mapList = connectivityPublic(dto);
////        if (mapList == null || mapList.isEmpty()) {
////            return Collections.emptyList();
////        }
////        // 流式处理优化，减少中间变量
////        return mapList.stream()
////                .map(map -> String.valueOf(map.get("bsm")))
////                .filter(bsm -> bsm != null && !bsm.trim().isEmpty())
////                .collect(Collectors.toList());
////    }
//    public Map<String, Object> queryMain(QueryPipelineLengthDTO dto) {
//        String tableName = dto.getTables().get(0);
//        // 获取第一个点位
//        List<Map<String, Object>> mapListStart = queryNearestLinesOne(dto.getFirstPoslon(), dto.getFirstPoslat(), dto.getTables().get(0));
//        Map<String, Object> startMap = mapListStart.get(0);
//        String pointStartX = startMap.get("x").toString();
//        String pointStartY = startMap.get("y").toString();
//        List<Map<String, Object>> mapListEnd = queryNearestLinesOne(dto.getEndPoslon(), dto.getEndPoslat(), dto.getTables().get(0));
//        Map<String, Object> endMapOne = mapListEnd.get(0);
//        String pointEndX = endMapOne.get("x").toString(); // 管道的点位
//        String pointEndY = endMapOne.get("y").toString(); // 管道的点位
//        // 先按原始顺序查询
//        List<String> bsmList = new ArrayList<>();
//        TableDataInfo mapList = connectivityPublic(dto);
//        if (mapList == null || StringUtils.isNull(mapList)) {
//            return null;
//        }
//        List<?> data = mapList.getData();
//        for (Object datum : data) {
//            log.info("datum===========:{}", datum);
//            // 将对象转换为Map（假设数据是以Map形式存储的）
//            if (datum instanceof Map) {
//                Map<?, ?> map = (Map<?, ?>) datum;
//                // 获取bsm属性值
//                Object bsmValue = map.get("bsm");
//                if (bsmValue != null) {
//                    String bsm = bsmValue.toString();
//
//                    bsmList.add(bsm);
//                }
//            }
//        }
//        Map<String, Object> objectMap = calculatePipeLength(pointStartX, pointStartY, pointEndX, pointEndY, bsmList, dto.getTables().get(0));
//        objectMap.put("geoJsonData", getGeoJson(tableName, bsmList));
//        return objectMap;
//    }
//
//
//    /**
//     * 添加一个返回识别参数
//     * 返回
//     *
//     * @param tableName
//     * @param bsmList
//     * @return geoJson
//     */
//    public Map<String, Object> geoJson(String tableName, List<String> bsmList) {
//        // 处理空列表：直接返回空结果或调整查询条件
//        if (bsmList == null || bsmList.isEmpty()) {
//            return Collections.emptyMap();
//        }
//        String bsmInClause = bsmList.stream()
//                .map(bsm -> bsm.trim().replace("'", "''"))
//                .collect(Collectors.joining("','"));
//        bsmInClause = "'" + bsmInClause + "'";
//        String sql = "WITH all_points AS (\n" +
//                "  SELECT\n" +
//                "    ( ST_DumpPoints ( ST_GeomFromText ( st_astext ( shape ) :: TEXT, 4528 ) ) ).geom AS pt \n" +
//                "        FROM " + tableName + " " +
//                "        WHERE bsm IN(" + bsmInClause + ") " +
//                "  ),\n" +
//                "  ordered_points AS ( SELECT pt FROM all_points ORDER BY ST_X ( pt ), ST_Y ( pt ) ) SELECT\n" +
//                "  row_to_json ( fc ) \n" +
//                "FROM\n" +
//                "  (\n" +
//                "  SELECT\n" +
//                "    'FeatureCollection' AS TYPE,\n" +
//                "    array_to_json (\n" +
//                "      ARRAY [ (\n" +
//                "      SELECT\n" +
//                "        row_to_json ( f ) \n" +
//                "      FROM\n" +
//                "        ( SELECT 'Feature' AS TYPE, ( st_asgeojson ( st_transform ( ST_MakeLine ( pt ), 4490 ) ) ) :: JSON AS geometry FROM ordered_points ) f \n" +
//                "      ) ] \n" +
//                "    ) AS features \n" +
//                "  ) fc;";
//        return DBHelper.NpgNormal_GetListDicNo1(
//                sql,
//                pgUrl,
//                pgUserName,
//                pgPassword
//        );
//    }
//
//
//    /**
//     * 计算两点之间的管段长度
//     *
//     * @param firstlon 起点经度  40509483.3933618
//     * @param firstlat 起点纬度 3490986.49281043
//     * @param endlon   终点经度  40509483.3933618
//     * @param endlat   终点纬度  3490986.49281043
//     * @param bsmList  管段标识列表（如 ["bsm1", "bsm2", "bsm3"...]）
//     * @return 包含长度信息的结果集
//     */
//    public Map<String, Object> calculatePipeLength(
//            String firstlon, String firstlat,
//            String endlon, String endlat,
//            List<String> bsmList, String tableName) {
//        // 校验入参
//        if (firstlon == null || firstlat == null || endlon == null || endlat == null
//                || bsmList == null || bsmList.isEmpty()) {
//            throw new IllegalArgumentException("投影坐标参数和管段列表不能为空");
//        }
//        try {
//            // 1. 从 POINT(...) 格式中提取纯数字坐标（返回字符串类型）
//            String x1 = extractCoordinate(firstlon, "起点经度");
//            String y1 = extractCoordinate(firstlat, "起点纬度");
//            String x2 = extractCoordinate(endlon, "终点经度");
//            String y2 = extractCoordinate(endlat, "终点纬度");
//
//            // 校验表名格式
//            if (!tableName.matches("^[a-zA-Z0-9_]+$")) {
//                throw new IllegalArgumentException("表名格式非法，只能包含字母、数字和下划线: " + tableName);
//            }
//            // 2. 处理管段列表，拼接为 'bsm1','bsm2' 格式
//            String bsmInClause = bsmList.stream()
//                    .map(bsm -> bsm.trim().replace("'", "''"))
//                    .collect(Collectors.joining("','"));
//            bsmInClause = "'" + bsmInClause + "'";
//            // 3. 构建 SQL（使用字符串拼接，避免 %f 格式符）
//            String sql = "WITH input_points AS (" +
//                    "  SELECT" +
//                    "    ST_SetSRID(ST_MakePoint(" + x1 + ", " + y1 + "), 4528) AS pt_start," +  // 直接拼接字符串
//                    "    ST_SetSRID(ST_MakePoint(" + x2 + ", " + y2 + "), 4528) AS pt_end" +
//                    "), " +
//                    "converted_lines AS (" +
//                    "  SELECT" +
//                    "    bsm," +
//                    "    qddh," +
//                    "    zddh," +
//                    "    ST_GeomFromEWKT('SRID=4528;' || ST_AsText(shape)) AS geom_line" +
//                    "  FROM " + tableName +
//                    "  WHERE bsm IN (" + bsmInClause + ")" +
//                    "), " +
//                    "merged_line AS (" +
//                    "  SELECT ST_LineMerge(ST_Union(geom_line)) AS geom_line" +
//                    "  FROM converted_lines" +
//                    "), " +
//                    "locates AS (" +
//                    "  SELECT" +
//                    "    ip.pt_start," +
//                    "    ip.pt_end," +
//                    "    ml.geom_line," +
//                    "    ST_LineLocatePoint(ml.geom_line, ip.pt_start) AS frac_start," +
//                    "    ST_LineLocatePoint(ml.geom_line, ip.pt_end) AS frac_end," +
//                    "    ST_Length(ml.geom_line) AS total_length" +
//                    "  FROM input_points ip, merged_line ml" +
//                    ")" +
//                    " SELECT " +
//                    "  abs(frac_end - frac_start) * total_length AS pipeLength" +
//                    " FROM locates";
//            return DBHelper.NpgNormal_GetListDicNo1(sql, pgUrl, pgUserName, pgPassword);
//        } catch (NumberFormatException e) {
//            throw new IllegalArgumentException("投影坐标格式错误，无法转换为数字: " + e.getMessage(), e);
//        } catch (Exception e) {
//            throw new RuntimeException("计算管段长度失败: " + e.getMessage(), e);
//        }
//    }
//
//    /**
//     * 从 POINT(...) 格式的字符串中提取坐标数值
//     * 例如："POINT(40509497.1665317 3490973.52221043)" → 提取数值部分
//     */
//    private String extractCoordinate(String wktStr, String paramName) {
//        if (wktStr == null || wktStr.trim().isEmpty()) {
//            throw new IllegalArgumentException(paramName + "不能为空");
//        }
//        String cleaned = wktStr.trim()
//                .replaceAll("[^0-9\\.\\- ]", "")
//                .trim();
//        String[] parts = cleaned.split("\\s+");
//        if (parts.length < 1) {
//            throw new NumberFormatException(paramName + "格式错误，无法提取坐标: " + wktStr);
//        }
//        return (parts[0]);
//    }
//
//
//    /**
//     * 通过两个点获取到所选的坐标入值
//     *
//     * @param dto
//     * @return
//     */
//    public TableDataInfo connectivityPublic(QueryPipelineLengthDTO dto) {
//        // TODO 第一次查询获取对应的线标识码，如果返回为空
//        List<Map<String, Object>> mapListStart = queryNearestLinesOne(
//                dto.getFirstPoslon(),
//                dto.getFirstPoslat(),
//                dto.getTables().get(0)
//        );
//        Map<String, Object> startMap = mapListStart.get(0);
//        String bsmStart = startMap.get("bsm").toString();
//        List<Map<String, Object>> mapListEnd = queryNearestLinesOne(
//                dto.getEndPoslon(),
//                dto.getEndPoslat(),
//                dto.getTables().get(0)
//        );
//        Map<String, Object> endMap = mapListEnd.get(0);
//        String bsmEnd = endMap.get("bsm").toString();
//        SimpleDateFormat simpleDateFormat1 = new SimpleDateFormat("yyyyMMdd HH:mm:ss");
//        String format1 = simpleDateFormat1.format(new Date());
//        System.out.println("执行结束时间：" +format1);
//        // TODO 连通性分析 （第二步）
//        int pageSize = dto.getPageSize();
//        int pageNum = dto.getPageNum();
//        String tableName = dto.getTables().get(0);
//        return getBsmDate(bsmStart, bsmEnd, tableName, pageSize, pageNum);
//    }
//
////    /**
////     * 获取分页的方法
////     *
////     * @param list
////     * @param pageSize
////     * @param pageNum
////     * @return
////     */
////    protected TableDataInfo getDataTableByMyself(List<?> list, Integer pageSize, Integer pageNum) {
////        if (pageSize == null) {
////            pageSize = 10;
////        }
////        if (pageNum == null) {
////            pageNum = 1;
////        }
////        TableDataInfo rspData = new TableDataInfo();
////        rspData.setCode(HttpStatus.SUCCESS);
////        rspData.setMsg("查询成功");
////        rspData.setData(list.stream().skip((pageNum - 1) * pageSize).limit(pageSize).collect(Collectors.toList()));
////        rspData.setTotal(new PageInfo(list).getTotal());
////        return rspData;
////    }
//
//
//    /**
//     * 根据起止标识码查询路径关联数据（含分页）
//     * @param startBsm 起始标识码
//     * @param endBsm 结束标识码
//     * @param tableName 表名
//     * @param pageSize 分页大小
//     * @param pageNum 页码
//     * @return 分页数据结果
//     */
//    public TableDataInfo getBsmDate(String startBsm, String endBsm, String tableName, Integer pageSize, Integer pageNum) {
//        if (StringUtils.isAnyBlank(startBsm, endBsm, tableName)) {
//            return createErrorTableData("参数错误：起始标识码、结束标识码、表名不能为空");
//        }
//        pageSize = (pageSize == null || pageSize <= 0) ? 10 : pageSize;
//        pageNum = (pageNum == null || pageNum <= 0) ? 1 : pageNum;
//        // 校验表名格式（简单防注入，可根据实际表名规则调整）
//        if (!isValidTableName(tableName)) {
//            return createErrorTableData("参数错误：表名格式不合法");
//        }
//        try {
//            // 2. 构建第一个查询SQL（从startBsm正向查询到endBsm）
//            String firstSql = buildPathSql(startBsm, endBsm, tableName);
//            // 执行查询
//            List<Map<String, Object>> firstResult = DBHelper.NpgNormal_GetListDic(firstSql, pgUrl, pgUserName, pgPassword);
//
//            // 3. 若第一个查询有结果，直接返回分页数据
//            if (ObjectUtils.isNotEmpty(firstResult)) {
//                return getDataTableByMyself(firstResult, pageSize, pageNum);
//            }
//            // 4. 第一个查询无结果，执行反向查询（从endBsm查询到startBsm）
//            String secondSql = buildPathSql(endBsm, startBsm, tableName);
//            List<Map<String, Object>> secondResult = DBHelper.NpgNormal_GetListDic(secondSql, pgUrl, pgUserName, pgPassword);
//            return getDataTableByMyself(secondResult, pageSize, pageNum);
//
//        } catch (Exception e) {
//            return createErrorTableData("查询失败：" + e.getMessage());
//        }
//    }
//
//    /**
//     * 构建路径查询SQL
//     * @param start 起始点标识码
//     * @param target 目标点标识码
//     * @param tableName 表名
//     * @return 拼接后的SQL语句
//     */
//    private String buildPathSql(String start, String target, String tableName) {
//        // 核心逻辑：递归查询路径 -> 拆分路径数组 -> 关联原表获取完整数据
//        String sql = "SELECT t.* " +
//                ", st_astext(st_transform(sde.st_geometry(st_astext(t.shape)::TEXT, 4528), 4490)) AS shapetext " +
//                ", t.xzq AS ssqy " +
//                ", t.zydw AS tcdw " +
//                "FROM " + tableName + " t " +
//                "JOIN ( " +
//                "  WITH RECURSIVE path_search AS ( " +
//                // 递归初始条件：从start点开始
//                "    SELECT bsm, qdkjbh, zdkjbh, ARRAY[bsm::TEXT]::TEXT[] AS path_bsm, 1 AS depth " +
//                "    FROM " + tableName + " " +
//                "    WHERE bsm = '" + escapeSql(start) + "' " + // 简单转义防注入
//                // 递归关联条件：通过qdkjbh和zdkjbh连接
//                "    UNION ALL " +
//                "    SELECT l.bsm, l.qdkjbh, l.zdkjbh, ps.path_bsm || l.bsm::TEXT, ps.depth + 1 " +
//                "    FROM " + tableName + " l " +
//                "    JOIN path_search ps ON l.qdkjbh = ps.zdkjbh " +
//                "    WHERE NOT l.bsm = ANY(ps.path_bsm) AND ps.depth < 100 " + // 防循环和深度限制
//                "  ), " +
//                // 提取目标路径并拆分数组
//                "  path_result AS ( SELECT path_bsm FROM path_search WHERE bsm = '" + escapeSql(target) + "' ) " +
//                "  SELECT UNNEST(path_bsm) AS path_item FROM path_result " +
//                ") t1 " +
//                "ON t.bsm = t1.path_item "; // 关联原表获取数据
//        return sql;
//    }
//
//    /**
//     * 简单SQL转义（防注入）
//     * 注意：生产环境建议结合数据库驱动的参数化查询
//     */
//    private String escapeSql(String value) {
//        if (value == null) {
//            return "";
//        }
//        // 转义单引号（避免闭合SQL字符串）
//        return value.replace("'", "''");
//    }
//
//    /**
//     * 校验表名合法性（防注入）
//     * 可根据实际业务的表名规则调整（如仅允许字母、数字、下划线）
//     */
//    private boolean isValidTableName(String tableName) {
//        // 表名规则：仅包含字母、数字、下划线，且长度合理
//        return tableName.matches("^[a-zA-Z0-9_]{1,50}$");
//    }
//
//    /**
//     * 创建错误结果
//     */
//    private TableDataInfo createErrorTableData(String message) {
//        TableDataInfo error = new TableDataInfo();
//        error.setCode(HttpStatus.ERROR);
//        error.setMsg(message);
//        error.setData(Collections.emptyList());
//        error.setTotal(0L);
//        return error;
//    }
//
//    /**
//     * 分页处理方法（保持原逻辑并增强健壮性）
//     */
//    public TableDataInfo getDataTableByMyself(List<?> list, Integer pageSize, Integer pageNum) {
//        List<?> dataList = (list == null) ? Collections.emptyList() : list;
//        long total = dataList.size();
//        // 计算分页起始位置（避免负数）
//        int start = Math.max((pageNum - 1) * pageSize, 0);
//        // 截取分页数据
//        List<?> pageData = dataList.stream()
//                .skip(start)
//                .limit(pageSize)
//                .collect(Collectors.toList());
//
//        TableDataInfo rspData = new TableDataInfo();
//        rspData.setCode(HttpStatus.SUCCESS);
//        rspData.setMsg("查询成功");
//        rspData.setData(pageData);
//        rspData.setTotal(total);
//        return rspData;
//    }
//
//
//    /**
//     * 返回geoJson结果封装
//     * 合并获取和解析geoJson的逻辑，直接重复调用
//     *
//     * @param tableName 表名
//     * @param bsmList   BSM列表
//     * @return 解析后的geoJson Map对象
//     */
//    public Map<String, Object> getGeoJson(String tableName, List<String> bsmList) {
//        Map<String, Object> objectMapGeoJson = geoJson(tableName, bsmList);
//        if (objectMapGeoJson == null || !objectMapGeoJson.containsKey("row_to_json")) {
//            return new HashMap<>();
//        }
//        String json = objectMapGeoJson.get("row_to_json").toString();
//        try {
//            return objectMapper.readValue(json, new TypeReference<Map<String, Object>>() {
//            });
//        } catch (Exception e) {
//            e.printStackTrace();
//            return new HashMap<>();
//        }
//    }
//
//
////    // 获取两个点位的数据
////    public List<Map<String, Object>> queryNearestLinesOne(String firstPoslon, String firstPoslat, String tableName) {
////        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMdd HH:mm:ss");
////        String format = simpleDateFormat.format(new Date());
////        System.out.println("执行开始时间：" +format);
////        if (firstPoslon == null || firstPoslat == null || firstPoslon.trim().isEmpty() || firstPoslat.trim().isEmpty()) {
////            throw new IllegalArgumentException("经纬度参数不能为空");
////        }
////        try {
////            // 保留字符串参数，直接使用原始格式
////            String lon = firstPoslon.trim();
////            String lat = firstPoslat.trim();
////            // 校验表名格式，防止SQL注入
////            if (!tableName.matches("^[a-zA-Z0-9_]+$")) {
////                throw new IllegalArgumentException("表名格式非法，只能包含字母、数字和下划线: " + tableName);
////            }
////            // 构建查询语句，拆分起点和终点坐标
////            String sql = "WITH input_point AS (" +
////                    "    SELECT " +
////                    "        ST_Transform(" +
////                    "            ST_SetSRID(ST_MakePoint(" + lon + ", " + lat + "), 4490), " +
////                    "            4528" +
////                    "        ) AS geom" +
////                    "), " +
////                    "converted_lines AS (" +
////                    "    SELECT" +
////                    "        qddh," +
////                    "        zddh," +
////                    "        bsm," +
////                    "        qdkjbh," +
////                    "        zdkjbh," +
////                    "        ST_GeomFromWKB(" +
////                    "            ST_AsBinary(shape)," +
////                    "            4528" +
////                    "        ) AS geom_line" +
////                    "    FROM " + tableName +
////                    "), " +
////                    "nearest_line AS (" +
////                    "    SELECT" +
////                    "        l.*," +
////                    "        ST_AsText(" +
////                    "            ST_Transform(" +
////                    "                ST_ClosestPoint(l.geom_line, ip.geom)," +
////                    "                4490" +
////                    "            )" +
////                    "        ) AS proj_pt_lonlat," +
////                    "        ST_AsText(" +
////                    "            ST_ClosestPoint(l.geom_line, ip.geom)" +
////                    "        ) AS proj_pt_wkt" +
////                    "    FROM converted_lines l, input_point ip" +
////                    "    ORDER BY ST_Distance(l.geom_line, ip.geom)" +
////                    "    LIMIT 1" +
////                    ")" +
////                    "SELECT *, " +
////                    "ST_X(ST_Geometry(proj_pt_wkt, 4549))::text AS x, " +
////                    "ST_Y(ST_Geometry(proj_pt_wkt, 4549))::text AS y " +
////                    "FROM nearest_line";
////            return DBHelper.NpgNormal_GetListDic(sql, pgUrl, pgUserName, pgPassword);
////        } catch (Exception e) {
////            throw new RuntimeException("查询失败: " + e.getMessage(), e);
////        }
////    }
//
//
//    // 获取两个点位的数据
//    public List<Map<String, Object>> queryNearestLinesOne(String firstPoslon, String firstPoslat, String tableName) {
//        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyyMMdd HH:mm:ss");
//        String format = simpleDateFormat.format(new Date());
//        System.out.println("执行开始时间：" +format);
//        if (firstPoslon == null || firstPoslat == null || firstPoslon.trim().isEmpty() || firstPoslat.trim().isEmpty()) {
//            throw new IllegalArgumentException("经纬度参数不能为空");
//        }
//        try {
//            // 保留字符串参数，直接使用原始格式
//            String lon = firstPoslon.trim();
//            String lat = firstPoslat.trim();
//            // 校验表名格式，防止SQL注入
//            if (!tableName.matches("^[a-zA-Z0-9_]+$")) {
//                throw new IllegalArgumentException("表名格式非法，只能包含字母、数字和下划线: " + tableName);
//            }
//            // 构建查询语句，拆分起点和终点坐标
//            String sql = "WITH input_point AS ( SELECT ST_Transform ( ST_SetSRID(ST_MakePoint("+ lon + ", " + lat + "), 4490), 4528 ) AS geom ),\n" +
//                    "converted_lines AS ( SELECT bsm, ST_GeomFromWKB ( ST_AsBinary ( shape ), 4528 ) AS geom_line FROM " + tableName +" ),\n" +
//                    "nearest_line AS (\n" +
//                    "  SELECT\n" +
//                    "    l.bsm,\n" +
//                    "    ST_AsText ( ST_ClosestPoint ( l.geom_line, ip.geom ) ) AS proj_pt_wkt \n" +
//                    "  FROM\n" +
//                    "    converted_lines l,\n" +
//                    "    input_point ip \n" +
//                    "  ORDER BY\n" +
//                    "    ST_Distance ( l.geom_line, ip.geom ) \n" +
//                    "    LIMIT 1 \n" +
//                    "  ) SELECT\n" +
//                    "  bsm,\n" +
//                    "  ST_X ( ST_Geometry ( proj_pt_wkt, 4549 ) ) :: TEXT AS x,\n" +
//                    "  ST_Y ( ST_Geometry ( proj_pt_wkt, 4549 ) ) :: TEXT AS y \n" +
//                    "FROM\n" +
//                    "  nearest_line;";
//            return DBHelper.NpgNormal_GetListDic(sql, pgUrl, pgUserName, pgPassword);
//        } catch (Exception e) {
//            throw new RuntimeException("查询失败: " + e.getMessage(), e);
//        }
//    }
//
//
//}
//
//
//
//
//
//
//
//
//
//
//
//
//
//
//
